﻿CREATE PROCEDURE [usix].[s_CustomerFile_Update]
	@Full bit = 0
AS

EXEC usix.s_StatusHistory_Local

--DROP TABLE usix.t_CustomerFile_Export_05
--TRUNCATE TABLE usix.t_CustomerFile_Export_05
--INSERT INTO usix.t_CustomerFile_Export_05
--SELECT     ApplID, DaysPastDue, LoanID, [Customer #], [Open Field#01], VantageID, VIN, Serial#, [G_L Date], GL_Days, [Open Date], [Car Year], Make, Model, 
--                      [Pymnt Schedule], [# of Payments], [# of PaymentsM], [Payment Amount], [Total Payments], [1st_ Payment Date], FirstPaymentDateCalc, 
--                      [Last Payment Date], [Next Payment Date], [# Pmts Remaining], [# Pmts RemainingM], [Balance Remaining], [Total Sales Price], [Finance Charges], 
--                      [Status Code], P_O_E_, Loan#_Comments, [Primary SSN], [Amt_ Fin__Disc_ Amt], [Interest Income], [Earned Discount], [Dealer Discount], 
--                      [Dealer Proceed], [Dealer Code], Dealer, DealerPhone, [Principal Balance], APR, Mileage, [Collector Id#], [Discount%], [Origination State Code], 
--                      Portfolio#, [Sales Rep_], [Investor Client], [Date L_C], [Joint Name], [Joint Address], [Joint City], [Joint State], [Joint Zipcode], [Joint SSN], 
--                      [Joint H Phone#], [Joint W Phone#], [Joint Cell#], [Partial Payment], [NSF Balance], [Late Chg Bal], [Apply Late Fee], Branch#, CompanyID, 
--                      CompanyID_Raw, CompanyName, [Bankruptcy Filed Y_N?], [Bankruptcy Type 07_13?], [Total Principal], DPD, DPD0, DSLP, PastDuePayments, 
--                      PastDueAmount, PaymentsMade, PaymentsMadeM, PaymentsExpectedM, PaymentsBehindM, DownPayment, [Princ/ChOff Ballance], [Repo Date], 
--                      [A_R Balance], [Resale Amt_], Chargeoff, [Final Repo Date], Retainage, [Other Chgs Bal], UsixID, [Title Received Y_N?], NDay, DPDCalc, 
--                      [Simple_Add-On], VINCheck, LateCharges, LatePmtsCount, RemBalance78, PayOff_TP, PayOff_Calc, PerDiem, NSF_Date, NSF_Count, NSF_Amount, 
--                      NSF_DaysSince, PaymentsMadeByPrinBal, NameLast, NameFirst, County, PaymentAmountM, DefermentsCount,St,[Zip Code]
----INTO usix.t_CustomerFile_Export_05
--FROM         usix.v_CustomerFile_Export_05

IF NOT  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[usix].[t_CustomerFile_Export]') AND name = N'IX_t_CustomerFile_Export_CustomeNo')
CREATE CLUSTERED INDEX [IX_t_CustomerFile_Export_CustomeNo] ON [usix].[t_CustomerFile_Export] 
(	[Customer #] ASC,	[CompanyID_Raw] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[usix].[t_CustomerFile_Export]') AND name = N'IX_t_CustomerFile_Export_LoanID')
CREATE NONCLUSTERED INDEX [IX_t_CustomerFile_Export_LoanID] ON [usix].[t_CustomerFile_Export] (
[LoanID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

-- First Payment[
DECLARE @C TABLE(	[Id #] varchar(8),	CompanyID tinyint,	Deferment int)
INSERT INTO @C
SELECT [Id #],[CompanyID]
,SUM(DATEDIFF(dd,dbo.clr_RegEx_MatchGroup(Remark,'Changed Due Date From: (\d+/\d+/\d+) To: \d+/\d+/\d+',1),
					dbo.clr_RegEx_MatchGroup(Remark,'Changed Due Date From: \d+/\d+/\d+ To: (\d+/\d+/\d+)',1)))
FROM [MotoBank].[usix].[t_Comment] (nolock)
WHERE Remark like 'Changed Due Date From%'
GROUP BY [Id #],CompanyID

BEGIN TRAN

IF @Full = 1 EXEC usix.s_CustomerFile_Update_FullInsert
ELSE				 EXEC usix.s_CustomerFile_UpdateLocal

UPDATE CE SET
	FirstPaymentDateCalc = DATEADD(dd,-C.Deferment,CE.[1st_ Payment Date]),
	DPDCalc = DPD + C.Deferment
FROM usix.t_CustomerFile_Export CE
INNER JOIN @C C ON CE.[Customer #] = C.[Id #] AND CE.CompanyID_Raw = C.CompanyID

UPDATE CE
SET PaymentsExpectedM = 
			CASE WHEN FirstPaymentDateCalc < getdate() - 1
					 THEN dbo.MinNum(dbo.MonthsApart(FirstPaymentDateCalc , GETDATE()-1) + 1 , [# of PaymentsM])
					 ELSE 0 END,
		VINCheck = dbo.clr_VINCheck(VIN),
PaymentsMadeByPrinBal = 
CASE [Total Principal] WHEN 0 THEN 0
ELSE
dbo.PmtsAdjMos(
	dbo.PaymentMadeByPrincipalBalance([Amt_ Fin__Disc_ Amt],[Payment Amount],[Open Date],FirstPaymentDateCalc,[# of Payments],PS.Frequency,APR,0,[Total Principal])
	,PS.Frequency,0
) END
FROM usix.t_CustomerFile_Export CE
INNER JOIN usix.t_PaymentSchedule PS ON PS.PS = CE.[Pymnt Schedule]

DECLARE @Subject varchar(512)
SET @Subject = '[usix].[s_CustomerFile_Update] '+CONVERT(varchar,@Full)

IF @@ERROR <> 0 BEGIN
	SET @Subject = @Subject + ' - ROLLBACK'
	ROLLBACK
END ELSE BEGIN
	SET @Subject = @Subject + ' - COMMIT'
	COMMIT
END

EXEC admin.s_Mail_System @Subject



